/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.thienloc.omap.session;

import com.thienloc.omap.entity.RequestQueue;
import com.thienloc.omap.entity.TrafficReport;
import com.thienloc.omap.entity.UserCmdCode;
import com.thienloc.omap.entity.report.SingleSubCpProduction;
import com.thienloc.omap.entity.report.ServiceProduction;
import com.thienloc.omap.entity.report.ShortCodeProduction;
import com.thienloc.omap.entity.report.TimeProduction;
import com.thienloc.omap.util.Constant;
import java.io.File;
import java.io.FileReader;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.TreeMap;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import org.apache.commons.io.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author GiangLT
 */
@Stateless
public class RequestQueueFacade extends AbstractFacade<RequestQueue> {

    private final Logger logger = LoggerFactory.getLogger(this.getClass().getSimpleName());
    @PersistenceContext(unitName = "omapPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public RequestQueueFacade() {
        super(RequestQueue.class);
    }

    public List<String> findAvaiableServices() {
        try {
            Query q = em.createQuery("SELECT distinct k.serviceName FROM RequestQueue k");
            List<String> result = (List<String>) q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<String>();
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<String> find(String region, String serviceName, Date startTime, Date endTime, String provider) {
        try {
            File configFile = new File(Constant.CONFIG_FOLDER + Constant.CONFIG_FILE_NAME);
            Properties properties = new Properties();
            properties.load(new FileReader(configFile));

            String jpqlFilePath = properties.getProperty("jpqlFile." + serviceName + "." + region + ".current");
            File jpqlFile = new File(jpqlFilePath);
            List<String> strQuerys = FileUtils.readLines(jpqlFile);
            StringBuilder sb = new StringBuilder();
            for (String query : strQuerys) {
                sb.append(query.trim());
                sb.append(" ");
            }

            Query q = em.createQuery(sb.toString(), String.class);
            q.setParameter("startTime", startTime);
            q.setParameter("endTime", endTime);
            q.setParameter("serviceName", serviceName);
            q.setParameter("provider", provider);
            List<String> result = (List<String>) q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<String>();
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<String> findTxtBaseUsers(List<String> syntaxList, Date startTime, Date endTime, String provider) {
        try {
            File configFile = new File(Constant.CONFIG_FOLDER + Constant.CONFIG_FILE_NAME);
            Properties properties = new Properties();
            properties.load(new FileReader(configFile));

            String jpqlFilePath = properties.getProperty("jpqlFile.txtbase.current");
            File jpqlFile = new File(jpqlFilePath);
            List<String> strQuerys = FileUtils.readLines(jpqlFile);
            StringBuilder sb = new StringBuilder();
            for (String query : strQuerys) {
                sb.append(query.trim());
                sb.append(" ");
            }

            Query q = em.createQuery(sb.toString(), String.class);
            q.setParameter("startTime", startTime);
            q.setParameter("endTime", endTime);
            q.setParameter("provider", provider);
            q.setParameter("syntaxList", syntaxList);

            List<String> result = (List<String>) q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<String>();
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<String> findUsersBySyntax(List<String> syntaxList, Date startTime, Date endTime, String serviceName, String provider) {
        try {
            File configFile = new File(Constant.CONFIG_FOLDER + Constant.CONFIG_FILE_NAME);
            Properties properties = new Properties();
            properties.load(new FileReader(configFile));

            String jpqlFilePath = properties.getProperty("jpqlFile.bySyntax.current");
            File jpqlFile = new File(jpqlFilePath);
            List<String> strQuerys = FileUtils.readLines(jpqlFile);
            StringBuilder sb = new StringBuilder();
            for (String query : strQuerys) {
                sb.append(query.trim());
                sb.append(" ");
            }

            Query q = em.createQuery(sb.toString(), String.class);
            q.setParameter("startTime", startTime);
            q.setParameter("endTime", endTime);
            q.setParameter("serviceName", serviceName);
            q.setParameter("provider", provider);
            q.setParameter("syntaxList", syntaxList);

            List<String> result = (List<String>) q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<String>();
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<RequestQueue> findByServiceNamesAndStatus(List<String> serviceNames, List<String> statuses, int maxResults) {
        try {
            StringBuilder query = new StringBuilder("SELECT r FROM RequestQueue r WHERE (r.serviceName IN ('");

            int serviceCnt = 0;
            for (String serviceName : serviceNames) {
                if (!serviceName.equals("None")) {
                    query.append(serviceName);
                    query.append("', '");
                    serviceCnt++;
                }
            }
            if (serviceCnt > 0) {
                query.delete(query.lastIndexOf(", '"), query.length());
                query.append(")");
            } else {
                query.append("')");
            }

            if (serviceNames.contains("None")) {
                query.append(" OR r.serviceName IS NULL");
            }
            query.append(") AND r.status IN ('");
            for (String status : statuses) {
                query.append(status);
                query.append("', '");
            }
            query.delete(query.lastIndexOf(", '"), query.length());
            query.append(")");

            TypedQuery<RequestQueue> q = em.createQuery(query.toString(), RequestQueue.class);
            q.setMaxResults(maxResults);
            List<RequestQueue> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<RequestQueue> findByStatus(List<String> statuses, int maxResults, boolean desc) {
        try {
            StringBuilder query = new StringBuilder("SELECT r FROM RequestQueue r WHERE r.status IN ('");
            for (String status : statuses) {
                query.append(status);
                query.append("', '");
            }
            query.delete(query.lastIndexOf(", '"), query.length());
            query.append(")");
            query.append(" ORDER BY r.receivedTime");
            if (desc) {
                query.append(" DESC");
            }

            TypedQuery<RequestQueue> q = em.createQuery(query.toString(), RequestQueue.class);
            q.setMaxResults(maxResults);
            List<RequestQueue> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<RequestQueue> findByStatusNullServiceOnly(List<String> statuses, int maxResults, boolean desc) {
        try {
            StringBuilder query = new StringBuilder("SELECT r FROM RequestQueue r WHERE r.status IN ('");
            for (String status : statuses) {
                query.append(status);
                query.append("', '");
            }
            query.delete(query.lastIndexOf(", '"), query.length());
            query.append(")");
            query.append(" AND (r.serviceName IS NULL OR r.serviceName = '')");
            query.append(" ORDER BY r.receivedTime");
            if (desc) {
                query.append(" DESC");
            }

            TypedQuery<RequestQueue> q = em.createQuery(query.toString(), RequestQueue.class);
            q.setMaxResults(maxResults);
            List<RequestQueue> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<TrafficReport> getTrafficReport(Date startDate, Date endDate, String serviceName, String cpNumber) {
        try {
            String queryStr =
                    "SELECT TRUNC(received_time), service_name, cp_number, COUNT(TRUNC(received_time))"
                    + " FROM request_queue"
                    + " WHERE (received_time BETWEEN ? AND ?)"
                    + " AND service_name LIKE ?"
                    + " AND cp_number = ?"
                    + " GROUP BY TRUNC(received_time), service_name, cp_number";
            Query query = em.createNativeQuery(queryStr);
            query.setParameter(1, startDate);
            query.setParameter(2, endDate);
            query.setParameter(3, "%" + serviceName + "%");
            query.setParameter(4, cpNumber);

            List result = query.getResultList();
            List<TrafficReport> trafficReports = new ArrayList<TrafficReport>(result.size());
            Iterator it = result.iterator();
            while (it.hasNext()) {
                Object[] fields = (Object[]) it.next();
                TrafficReport report = new TrafficReport();
                report.setDate((Date) fields[0]);
                report.setService((String) fields[1]);
                report.setCpNumber((String) fields[2]);
                report.setTotalMessage(((BigDecimal) fields[3]).intValue());

                trafficReports.add(report);
            }
            return trafficReports;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<String> getDeliverProviders() throws Exception {
        try {
            TypedQuery<String> q = em.createNamedQuery("RequestQueue.getDeliverProviders", String.class);
            List<String> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<String>(0);
        }
    }

    public List<String> getServiceNames() throws Exception {
        try {
            TypedQuery<String> q = em.createNamedQuery("RequestQueue.getServiceNames", String.class);
            List<String> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<String>(0);
        }
    }

    public List<String> getCpNumbers() throws Exception {
        try {
            TypedQuery<String> q = em.createNamedQuery("RequestQueue.getCpNumbers", String.class);
            List<String> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<String>(0);
        }
    }

    public List<RequestQueue> findByResponseContent(String responseContent) {
        try {
            String query = "select * from request_queue r where r.response_content = '"
                    + responseContent.replaceAll("\\r\\n", "'||chr(13)||chr(10)||'");
            String q;
            if (query.endsWith("||'")) {
                q = query.substring(0, query.lastIndexOf("||'"));
            } else {
                q = query;
            }
            q += "'";
            logger.info(q);
            Query qr = em.createNativeQuery(q, RequestQueue.class);
            List<RequestQueue> result = qr.getResultList();
            return result;
        } catch (NoResultException ex) {
            return new ArrayList<RequestQueue>(0);
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<RequestQueue>(0);
        }
    }

    public List<ShortCodeProduction> countByShortCode(Date startDate, Date endDate, String[] blackCmds, String[] whiteCmds) {
        DateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
        StringBuilder whiteCmdCodeCriteria = buildWhiteCmdCodeCriteria(whiteCmds);
        StringBuilder blackCmdCodeCriteria = buildBlackCmdCodeCriteria(blackCmds);
        try {
            StringBuilder queryStr = new StringBuilder();
            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append("SUM (\"6071\") \"6071\" , SUM (\"6171\") \"6171\", SUM (\"6271\") \"6271\", ");
            queryStr.append(" SUM (\"6371\") \"6371\", SUM (\"6471\") \"6471\", SUM (\"6571\") \"6571\", SUM (\"6671\") \"6671\", ");
            queryStr.append(" SUM (\"6771\") \"6771\" FROM ( ");


            /// select on REQUEST_QUEUE
            queryStr.append(" SELECT DECODE(deliver_provider, 'smsgate_viettel6771', 'Viettel', 'smsgate_mobifone6771', 'Mobifone', 'Vinaphone') deliver_provider, ");
            queryStr.append("SUM (\"6071\") \"6071\" , SUM (\"6171\") \"6171\", SUM (\"6271\") \"6271\", ");
            queryStr.append(" SUM (\"6371\") \"6371\", SUM (\"6471\") \"6471\", SUM (\"6571\") \"6571\", SUM (\"6671\") \"6671\", ");
            queryStr.append(" SUM (\"6771\") \"6771\" FROM ( ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" COUNT (*) \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6071'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", COUNT (*) \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6171'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", COUNT (*) \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6271'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", COUNT (*) \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6371'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", COUNT (*) \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6471'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",COUNT (*) \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6571'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", COUNT (*) \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6671'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", COUNT (*) \"6771\"");
            queryStr.append(" FROM request_queue");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6771'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");

            queryStr.append(") GROUP BY deliver_provider");
            /// end select on REQUEST_QUEUE

            queryStr.append(" union all ");

            /// select on REQUEST_QUEUE_HIST
            queryStr.append(" SELECT DECODE(deliver_provider, 'smsgate_viettel6771', 'Viettel', 'smsgate_mobifone6771', 'Mobifone', 'Vinaphone') deliver_provider, ");
            queryStr.append("SUM (\"6071\") \"6071\" , SUM (\"6171\") \"6171\", SUM (\"6271\") \"6271\", ");
            queryStr.append(" SUM (\"6371\") \"6371\", SUM (\"6471\") \"6471\", SUM (\"6571\") \"6571\", SUM (\"6671\") \"6671\", ");
            queryStr.append(" SUM (\"6771\") \"6771\" FROM ( ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" COUNT (*) \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6071'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", COUNT (*) \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6171'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", COUNT (*) \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6271'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", COUNT (*) \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6371'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", COUNT (*) \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6471'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",COUNT (*) \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6571'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", COUNT (*) \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6671'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            queryStr.append(" union all ");

            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", COUNT (*) \"6771\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%6771'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");

            queryStr.append(") GROUP BY deliver_provider");
            //end select from REQUEST_QUEUE_HIST

            queryStr.append(") GROUP BY deliver_provider");
            logger.trace(queryStr.toString());
            Query query = em.createNativeQuery(queryStr.toString());
            List resultList = query.getResultList();
            List<ShortCodeProduction> result = new ArrayList<ShortCodeProduction>();
            for (Object row : resultList) {
                Object[] fields = (Object[]) row;
                ShortCodeProduction production = new ShortCodeProduction();
                production.setOpertator(fields[0].toString());
                production.setP6071(((BigDecimal) fields[1]).intValue());
                production.setP6171(((BigDecimal) fields[2]).intValue());
                production.setP6271(((BigDecimal) fields[3]).intValue());
                production.setP6371(((BigDecimal) fields[4]).intValue());
                production.setP6471(((BigDecimal) fields[5]).intValue());
                production.setP6571(((BigDecimal) fields[6]).intValue());
                production.setP6671(((BigDecimal) fields[7]).intValue());
                production.setP6771(((BigDecimal) fields[8]).intValue());
                result.add(production);
            }

            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    private StringBuilder buildBlackCmdCodeCriteria(String[] blackCmds) {
        if (blackCmds != null && blackCmds.length > 0) {
            StringBuilder blackCmdCodeCriteria = new StringBuilder();
            blackCmdCodeCriteria.append(" ( ");
            for (int i = 0; i < blackCmds.length; i++) {
                blackCmdCodeCriteria.append("UPPER(request_detail) NOT LIKE '").append(blackCmds[i].toUpperCase()).append("%'");
                if (i != blackCmds.length - 1) {
                    blackCmdCodeCriteria.append(" AND ");
                }
            }
            blackCmdCodeCriteria.append(" ) ");
            return blackCmdCodeCriteria;
        }
        return null;
    }

    private StringBuilder buildWhiteCmdCodeCriteria(String[] whiteCmds) {
        if (whiteCmds != null && whiteCmds.length > 0) {
            StringBuilder whiteCmdCodeCriteria = new StringBuilder();
            whiteCmdCodeCriteria.append(" ( ");
            for (int i = 0; i < whiteCmds.length; i++) {
                whiteCmdCodeCriteria.append("UPPER(request_detail) LIKE '").append(whiteCmds[i].toUpperCase()).append("%'");
                if (i != whiteCmds.length - 1) {
                    whiteCmdCodeCriteria.append(" OR ");
                }
            }
            whiteCmdCodeCriteria.append(" ) ");
            return whiteCmdCodeCriteria;
        }
        return null;
    }

    public SingleSubCpProduction countSubCpProduction(Date startDate, Date endDate, UserCmdCode userCmdCode, String shortCode) {
        String blackCmdList = userCmdCode.getBlackCmdList();
        StringBuilder blackCmdCodeCriteria = null;
        if (blackCmdList != null && !blackCmdList.isEmpty()) {
            blackCmdCodeCriteria = buildBlackCmdCodeCriteria(blackCmdList.split(","));
        }

        String whiteCmdList = userCmdCode.getWhiteCmdList();
        StringBuilder whiteCmdCodeCriteria = null;
        if (whiteCmdList != null && !whiteCmdList.isEmpty()) {
            whiteCmdCodeCriteria = buildWhiteCmdCodeCriteria(whiteCmdList.split(","));
        }
        
        DateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
        try {
            StringBuilder queryStr = new StringBuilder();
            queryStr.append(" SELECT deliver_provider, ");
            queryStr.append(" SUM (\"").append(shortCode).append("\") \"").append(shortCode).append("\" FROM ( ");

            /// select on REQUEST_QUEUE
            queryStr.append(" SELECT DECODE(deliver_provider, 'smsgate_viettel6771', 'Viettel', 'smsgate_mobifone6771', 'Mobifone', 'Vinaphone') deliver_provider, ");
            queryStr.append(" COUNT (*) \"").append(shortCode).append("\"");
            queryStr.append(" FROM request_queue");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%").append(shortCode).append("'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            /// end select on REQUEST_QUEUE

            queryStr.append(" UNION ALL ");

            /// select on REQUEST_QUEUE_HIST
            queryStr.append(" SELECT DECODE(deliver_provider, 'smsgate_viettel6771', 'Viettel', 'smsgate_mobifone6771', 'Mobifone', 'Vinaphone') deliver_provider, ");
            queryStr.append(" COUNT (*) \"").append(shortCode).append("\"");
            queryStr.append(" FROM request_queue_hist");
            queryStr.append(" WHERE service_name IS NOT NULL ");
            queryStr.append(" AND received_time BETWEEN to_date('").append(df.format(startDate)).append("', 'dd-mm-yyyy hh24:mi:ss') and to_date('").append(df.format(endDate)).append("', 'dd-mm-yyyy hh24:mi:ss') ");
            queryStr.append(" AND cp_number like  '%").append(shortCode).append("'");
            if (whiteCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(whiteCmdCodeCriteria);
            }
            if (blackCmdCodeCriteria != null) {
                queryStr.append(" AND ").append(blackCmdCodeCriteria);
            }
            queryStr.append(" GROUP BY deliver_provider");
            //end select from REQUEST_QUEUE_HIST

            queryStr.append(") GROUP BY deliver_provider");
            logger.trace(queryStr.toString());
            Query query = em.createNativeQuery(queryStr.toString());
            List resultList = query.getResultList();
            
            SingleSubCpProduction production = new SingleSubCpProduction();
            production.setSubCpName(userCmdCode.getUsername());
            for (Object row : resultList) {
                Object[] fields = (Object[]) row;
                String deliverProvider = fields[0].toString();
                if (deliverProvider.equalsIgnoreCase("Viettel")){
                    production.setProductionOfViettel(((BigDecimal) fields[1]).intValue());
                } else if (deliverProvider.equalsIgnoreCase("Vinaphone")){
                    production.setProductionOfVinaphone(((BigDecimal) fields[1]).intValue());
                } else if (deliverProvider.equalsIgnoreCase("Mobifone")){
                    production.setProductionOfMobifone(((BigDecimal) fields[1]).intValue());
                } 
            }

            return production;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<TimeProduction> countByTime(String[] dates, String datePattern) {
        StringBuilder dateCriteria = new StringBuilder();
        dateCriteria.append(" to_char(received_time, '").append(datePattern).append("') in (");
        for (String date : dates) {
            dateCriteria.append("'").append(date).append("', ");
        }
        dateCriteria.delete(dateCriteria.lastIndexOf(", "), dateCriteria.length());
        dateCriteria.append(") ");

        try {
            StringBuilder queryStr = new StringBuilder();
            queryStr.append(" SELECT received_time, SUM (\"6071\") \"6071\" , SUM (\"6171\") \"6171\", SUM (\"6271\") \"6271\", ");
            queryStr.append(" SUM (\"6371\") \"6371\", SUM (\"6471\") \"6471\", SUM (\"6571\") \"6571\", SUM (\"6671\") \"6671\", ");
            queryStr.append(" SUM (\"6771\") \"6771\" FROM ( ");

            //start
            queryStr.append(" SELECT received_time, SUM (\"6071\") \"6071\" , SUM (\"6171\") \"6171\", SUM (\"6271\") \"6271\", ");
            queryStr.append(" SUM (\"6371\") \"6371\", SUM (\"6471\") \"6471\", SUM (\"6571\") \"6571\", SUM (\"6671\") \"6671\", ");
            queryStr.append(" SUM (\"6771\") \"6771\" FROM ( ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" COUNT (*) \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6071'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", COUNT (*) \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6171'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", COUNT (*) \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6271'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", COUNT (*) \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6371'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", COUNT (*) \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6471'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",COUNT (*) \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6571'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", COUNT (*) \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6671'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", COUNT (*) \"6771\"");
            queryStr.append(" FROM request_queue WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6771'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");

            queryStr.append(") GROUP BY received_time");



            queryStr.append(" union all ");



            queryStr.append(" SELECT received_time, SUM (\"6071\") \"6071\" , SUM (\"6171\") \"6171\", SUM (\"6271\") \"6271\", ");
            queryStr.append(" SUM (\"6371\") \"6371\", SUM (\"6471\") \"6471\", SUM (\"6571\") \"6571\", SUM (\"6671\") \"6671\", ");
            queryStr.append(" SUM (\"6771\") \"6771\" FROM ( ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" COUNT (*) \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6071'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", COUNT (*) \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6171'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", COUNT (*) \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6271'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", COUNT (*) \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6371'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", COUNT (*) \"6471\",0 \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6471'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",COUNT (*) \"6571\", 0 \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6571'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", COUNT (*) \"6671\", 0 \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6671'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");
            queryStr.append(" union all ");

            queryStr.append(" SELECT to_char(received_time, '").append(datePattern).append("') received_time, ");
            queryStr.append(" 0 \"6071\", 0 \"6171\", 0 \"6271\", 0 \"6371\", 0 \"6471\",0 \"6571\", 0 \"6671\", COUNT (*) \"6771\"");
            queryStr.append(" FROM request_queue_hist WHERE ");
            queryStr.append(dateCriteria);
            queryStr.append(" AND service_name IS NOT NULL ");
            queryStr.append(" AND cp_number like  '%6771'");
            queryStr.append(" GROUP BY to_char(received_time, '").append(datePattern).append("')");

            queryStr.append(") GROUP BY received_time");
            //end

            queryStr.append(") GROUP BY received_time ORDER BY received_time");

            Query query = em.createNativeQuery(queryStr.toString());
            List resultList = query.getResultList();
            List<TimeProduction> result = new ArrayList<TimeProduction>();
            for (Object row : resultList) {
                Object[] fields = (Object[]) row;
                TimeProduction production = new TimeProduction();
                production.setDate(fields[0].toString());
                production.setP6071(((BigDecimal) fields[1]).intValue());
                production.setP6171(((BigDecimal) fields[2]).intValue());
                production.setP6271(((BigDecimal) fields[3]).intValue());
                production.setP6371(((BigDecimal) fields[4]).intValue());
                production.setP6471(((BigDecimal) fields[5]).intValue());
                production.setP6571(((BigDecimal) fields[6]).intValue());
                production.setP6671(((BigDecimal) fields[7]).intValue());
                production.setP6771(((BigDecimal) fields[8]).intValue());
                result.add(production);
            }

            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<ServiceProduction> countByService(String[] services, Date date, String sqlDatePattern) {
        DateFormat df = new SimpleDateFormat(sqlDatePattern);

        try {
            StringBuilder queryStr = new StringBuilder("SELECT ");
            queryStr.append(" deliver_provider,");
            for (String service : services) {
                queryStr.append(" SUM(").append(service).append(") ").append(service).append(", ");
            }
            queryStr.delete(queryStr.lastIndexOf(", "), queryStr.length());
            queryStr.append(" FROM ( ");

            for (String service : services) {
                queryStr.append(" SELECT deliver_provider, ");
                for (String serviceInLoop : services) {
                    if (serviceInLoop.equals(service)) {
                        queryStr.append(" COUNT(*) ");
                    } else {
                        queryStr.append(" 0 ");
                    }
                    queryStr.append(serviceInLoop).append(", ");
                }

                queryStr.delete(queryStr.lastIndexOf(", "), queryStr.length());

                queryStr.append(" FROM request_queue");
                queryStr.append(" WHERE received_time like '").append(df.format(date)).append("' AND service_name = '").append(service).append("'");
                queryStr.append(" GROUP BY deliver_provider");
                queryStr.append(" union all ");
            }
            queryStr.delete(queryStr.lastIndexOf(" union all "), queryStr.length());
            queryStr.append(") GROUP BY deliver_provider");

            Query query = em.createNativeQuery(queryStr.toString());
            List resultList = query.getResultList();
            List<ServiceProduction> result = new ArrayList<ServiceProduction>();
            for (Object row : resultList) {
                Object[] fields = (Object[]) row;
                ServiceProduction production = new ServiceProduction();
                production.setOpertator(fields[0].toString());
                for (int i = 0; i < services.length; i++) {
                    production.put(services[i], ((BigDecimal) fields[i + 1]).intValue());
                }
                result.add(production);
            }

            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<RequestQueue> findByMsisdn(String msisdn) {
        try {
            TypedQuery<RequestQueue> q = em.createNamedQuery("RequestQueue.findByMsisdn", RequestQueue.class);
            q.setParameter("msisdn", msisdn);
            List<RequestQueue> result = q.getResultList();
            return result;
        } catch (NoResultException ex) {
            logger.info("No result found");
            return new ArrayList<RequestQueue>(0);
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<RequestQueue>(0);
        }
    }

    public Map<String, Integer> countProductionOfOperators(Date startTime, Date endTime, String cpNumber, List<String> deliverProviders) {
        try {
            StringBuilder queryStr = new StringBuilder();
            queryStr.append(" SELECT COUNT(r.request_id), TRIM(UPPER(r.request_detail)) ");
            queryStr.append(" FROM request_queue r ");
            queryStr.append(" WHERE (r.received_time BETWEEN ? AND ?) ");
            queryStr.append(" AND r.cp_number = ? ");
            queryStr.append(" AND service_name IS NOT NULL ");

            queryStr.append(" AND r.deliver_provider IN (");
            for (String deliverProvider : deliverProviders) {
                queryStr.append("'").append(deliverProvider).append("',");
            }
            queryStr.delete(queryStr.lastIndexOf(","), queryStr.length());
            queryStr.append(") ");

            queryStr.append(" GROUP BY TRIM(UPPER(r.request_detail)) ");

            logger.trace(queryStr.toString());
            Query query = em.createNativeQuery(queryStr.toString());
            query.setParameter(1, startTime);
            query.setParameter(2, endTime);
            query.setParameter(3, cpNumber);

            List result = query.getResultList();
            Map<String, Integer> resultMap = new TreeMap<String, Integer>();
            Iterator it = result.iterator();
            while (it.hasNext()) {
                Object[] fields = (Object[]) it.next();
                String requestDetail = (String) fields[1];
                if (requestDetail != null && !requestDetail.trim().isEmpty()) {
                    Integer count = ((BigDecimal) fields[0]).intValue();
                    if (resultMap.containsKey(requestDetail)) {
                        resultMap.put(requestDetail, count + resultMap.get(requestDetail));
                    } else {
                        resultMap.put(requestDetail, count);
                    }
                }
            }
            return resultMap;
        } catch (NoResultException ex) {
            return new HashMap<String, Integer>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new HashMap<String, Integer>();
        }
    }
    
    public Map<String, Integer> countMoProduction(Date startTime, Date endTime, List<String> deliverProviders) {
        try {
            StringBuilder queryStr = new StringBuilder();
            queryStr.append(" SELECT COUNT(r.request_id), TRIM(UPPER(r.request_detail)) ");
            queryStr.append(" FROM request_queue r ");
            queryStr.append(" WHERE (r.received_time BETWEEN ? AND ?) ");
            queryStr.append(" AND service_name IS NOT NULL ");

            queryStr.append(" AND r.deliver_provider IN (");
            for (String deliverProvider : deliverProviders) {
                queryStr.append("'").append(deliverProvider).append("',");
            }
            queryStr.delete(queryStr.lastIndexOf(","), queryStr.length());
            queryStr.append(") ");

            queryStr.append(" GROUP BY TRIM(UPPER(r.request_detail)) ");

            logger.trace(queryStr.toString());
            Query query = em.createNativeQuery(queryStr.toString());
            query.setParameter(1, startTime);
            query.setParameter(2, endTime);

            List result = query.getResultList();
            Map<String, Integer> resultMap = new TreeMap<String, Integer>();
            Iterator it = result.iterator();
            while (it.hasNext()) {
                Object[] fields = (Object[]) it.next();
                String requestDetail = (String) fields[1];
                if (requestDetail != null && !requestDetail.trim().isEmpty()) {
                    Integer count = ((BigDecimal) fields[0]).intValue();
                    if (resultMap.containsKey(requestDetail)) {
                        resultMap.put(requestDetail, count + resultMap.get(requestDetail));
                    } else {
                        resultMap.put(requestDetail, count);
                    }
                }
            }
            return resultMap;
        } catch (NoResultException ex) {
            return new HashMap<String, Integer>();
        } catch (Exception ex) {
            logger.error("", ex);
            return new HashMap<String, Integer>();
        }
    }

    public Integer countProductionOfOperators(String cpNumber, List<String> requestDetails, List<String> deliverProviders, Date startTime, Date endTime, Integer mdnSuffixStartRange, Integer mdnSuffixEndRange) {
        try {
            StringBuilder queryStr = new StringBuilder();
            queryStr.append(" SELECT COUNT(r.request_id) ");
            queryStr.append(" FROM request_queue r ");
            queryStr.append(" WHERE (r.received_time BETWEEN ? AND ?) ");
            queryStr.append(" AND service_name IS NOT NULL ");

            int suffixLength = mdnSuffixEndRange.toString().length();
            queryStr.append(" AND TO_NUMBER(SUBSTR(msisdn, LENGTH(msisdn)-");
            queryStr.append(suffixLength - 1);
            queryStr.append(", ");
            queryStr.append(suffixLength);
            queryStr.append(")) >= ");
            queryStr.append(mdnSuffixStartRange);
            queryStr.append(" AND TO_NUMBER(SUBSTR(msisdn, LENGTH(msisdn)-");
            queryStr.append(suffixLength - 1);
            queryStr.append(", ");
            queryStr.append(suffixLength);
            queryStr.append(")) <= ");
            queryStr.append(mdnSuffixEndRange);

            queryStr.append(" AND TRIM(UPPER(r.request_detail)) IN ( ");
            for (int i = 0; i < requestDetails.size(); i++) {
                queryStr.append("'").append(requestDetails.get(i)).append("',");
            }
            queryStr.deleteCharAt(queryStr.lastIndexOf(","));
            queryStr.append(") ");

            queryStr.append(" AND deliver_provider IN (");
            for (String deliverProvider : deliverProviders) {
                queryStr.append("'").append(deliverProvider).append("',");
            }
            queryStr.delete(queryStr.lastIndexOf(","), queryStr.length());
            queryStr.append(") ");
            queryStr.append(" AND cp_number = ? ");

            logger.trace(queryStr.toString());

            Query query = em.createNativeQuery(queryStr.toString());
            query.setParameter(1, startTime);
            query.setParameter(2, endTime);
            query.setParameter(3, cpNumber);
//            int paramIndex = 3;
//            for (String requestDetail : requestDetails) {
//                query.setParameter(paramIndex++, requestDetail);
//            }

            return ((BigDecimal) query.getSingleResult()).intValue();
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    //SELECT COUNT(r.request_id), TO_CHAR(received_time, 'dd-mm-yyyy'), TRIM(UPPPER(r.request_detail))
    //FROM request_queue
    //WHERE 
    //GROUP BY TO_CHAR(received_time, 'dd-mm-yyyy'), TRIM(UPPPER(r.request_detail))
    public List countProductionOfService(List<String> deliverProviders, String sqlDatePattern, List<Date[]> timeRanges) {
        try {
            StringBuilder queryStr = new StringBuilder();
            SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
            for (Date[] timeRange : timeRanges) {
                String startTime = sdf.format(timeRange[0]);
                String endTime = sdf.format(timeRange[1]);

                queryStr.append("SELECT COUNT(r.request_id), TO_CHAR(r.received_time, '");
                queryStr.append(sqlDatePattern);
                queryStr.append("'), TRIM(UPPER(r.request_detail)) ");
                queryStr.append(" FROM request_queue r ");
                queryStr.append(" WHERE (r.received_time BETWEEN TO_DATE('");
                queryStr.append(startTime);
                queryStr.append("', 'dd-mm-yyyy hh24:mi:ss') AND TO_DATE('");
                queryStr.append(endTime);
                queryStr.append("', 'dd-mm-yyyy hh24:mi:ss')) ");
                queryStr.append(" AND service_name IS NOT NULL ");
                queryStr.append(" AND deliver_provider IN (");
                for (String deliverProvider : deliverProviders) {
                    queryStr.append("'").append(deliverProvider).append("',");
                }
                queryStr.delete(queryStr.lastIndexOf(","), queryStr.length());
                queryStr.append(") ");
                queryStr.append(" GROUP BY TO_CHAR(received_time, '");
                queryStr.append(sqlDatePattern);
                queryStr.append("'), TRIM(UPPER(r.request_detail)) ");
                queryStr.append(" UNION ALL ");
            }
            queryStr.delete(queryStr.lastIndexOf(" UNION ALL "), queryStr.length());

            logger.trace(queryStr.toString());
            Query query = em.createNativeQuery(queryStr.toString());
            return query.getResultList();
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<RequestQueue> findByMsisdnInDateRange(String msisdn, Date startDate, Date endDate) {
        try {
            TypedQuery<RequestQueue> q = em.createNamedQuery("RequestQueue.findByMsisdnInDateRange", RequestQueue.class);
            q.setParameter("msisdn", "%" + msisdn + "%");
            q.setParameter("startDate", startDate);
            q.setParameter("endDate", endDate);
            return q.getResultList();
        } catch (NoResultException ex) {
            return new ArrayList<RequestQueue>(0);
        } catch (Exception ex) {
            logger.error("", ex);
            return new ArrayList<RequestQueue>(0);
        }
    }
}
